package com.qingcheng.dao;

import com.qingcheng.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportMapper extends Mapper<CategoryReport> {

    /**
     * 定义根据日期查询一条统计数据记录
     * @return
     */
    @Select("SELECT oi.category_id1 categoryId1 ,oi.category_id2 categoryId2,oi.category_id3 categoryId3 ,SUM(oi.pay_money) money ,SUM(oi.num) num ,DATE_FORMAT(o.pay_time,'%Y-%m-%d') countDate " +
            "FROM tb_order o INNER JOIN tb_order_item oi " +
            "ON o.id=oi.order_id  " +
            "WHERE o.pay_status='1'  AND o.is_delete='0' AND DATE_FORMAT(o.pay_time,'%Y-%m-%d')=#{countDate}  " +
            "GROUP BY oi.category_id1 ,oi.category_id2,oi.category_id3 ,DATE_FORMAT(o.pay_time,'%Y-%m-%d') ")
    public List<CategoryReport> countCategory(@Param("countDate") LocalDate date);

    @Select("SELECT c.name name ,SUM(cr.num) num ,SUM(cr.money) money " +
            "FROM tb_category_report cr INNER JOIN v_category1 c " +
            "ON cr.category_id1 =c.id " +
            "WHERE cr.count_date>=#{date1} AND cr.count_date<=#{date2} " +
            "GROUP BY cr.category_id1 ")
    public List<Map> totalCategory(@Param("date1") String date1,@Param("date2") String date2);
}
